{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e85f3e86-d21b-4e3d-9311-4a8b79c7e6f7",
   "metadata": {},
   "source": [
    "# Pandas示例"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d1490af-1274-4907-bfe0-988ae532481b",
   "metadata": {},
   "source": [
    "## Series示例"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62bcd31a-22d8-4048-8924-4409cebf28c0",
   "metadata": {},
   "source": [
    "### 创建Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be2e88b3-07f6-4346-9e78-9bf607aeb3c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建Series的示例\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "s = pd.Series([1, 3, 5, np.nan, 6, 8]) # 引入numpy处理NaN，且作用默认的数字索引\n",
    "print(s)\n",
    "\n",
    "s2 = pd.Series([10, 20, 30, 40], index=['a', 'b', 'c', 'd'])  # 指定索引\n",
    "print(s2)\n",
    "\n",
    "data = {'apple': 30, 'banana': 50, 'cherry': 20}   # 基于字典创建Series\n",
    "s3 = pd.Series(data)\n",
    "print(f\"\\n基于字典创建Series:\\n{s3}\")\n",
    "\n",
    "arr = np.array([1, 2, 3])\n",
    "s4 = pd.Series(arr, index=['a', 'b', 'c'])\n",
    "print(f\"\\n基于NumPy数组创建Series:\\n{s4}\")\n",
    "\n",
    "print(\"\\n使用标签对元素进行访问\")\n",
    "print(s2['b'])      # 使用标签访问\n",
    "print(s2[1])        # 使用位置访问 (如果索引是整数，注意区分)，即将废弃，建议使用下面的iloc[]方式替代\n",
    "print(s2.iloc[1])\n",
    "print(s2[['a', 'c']]) # 使用标签列表访问多个元素\n",
    "\n",
    "print(\"\\n切片\")\n",
    "print(s2[0:2])      # 使用位置切片 (左闭右开)\n",
    "print(s2['a':'c'])  # 使用标签切片 (左闭右闭)\n",
    "\n",
    "print(s2 * 2)  # 数学运算乘\n",
    "print(s2 + 10)  # 数学运算加\n",
    "\n",
    "s4 = pd.Series([1, 2, 3], index=['a', 'b', 'e'])\n",
    "print(s2 + s4) # 没有对齐的索引位置会产生 NaN"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30a08c15-062a-460a-b9ad-83ab327793c1",
   "metadata": {},
   "source": [
    "### Series支持的方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42bd2892-e78f-4d6f-a9f9-eddbc05c4979",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Series支持的方法示例\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# --- 1. 创建 Series ---\n",
    "print(\"--- 1. 创建 Series ---\")\n",
    "# 假设我们有一个班级学生的考试分数数据，其中可能包含缺失值\n",
    "scores_data = [85, 92, np.nan, 78, 95, 60, 88, 92, 78, 90, 75, np.nan]\n",
    "# 创建Series，并指定一些自定义索引\n",
    "student_ids = [f'S{i:02d}' for i in range(1, len(scores_data) + 1)]\n",
    "scores = pd.Series(scores_data, index=student_ids, name='考试分数')\n",
    "print(\"原始 Series:\")\n",
    "print(scores)\n",
    "print(\"-\" * 30)\n",
    "\n",
    "# --- 2. 访问与选择 ---\n",
    "print(\"\\n--- 2. 访问与选择 ---\")\n",
    "print(\"选择索引为 'S03' 的分数:\", scores.loc['S03'])\n",
    "print(\"选择索引为 0 的分数 (使用整数位置):\", scores.iloc[0])\n",
    "print(\"选择前 3 个分数 (切片):\")\n",
    "print(scores[:3])\n",
    "print(\"选择分数大于 90 的学生:\")\n",
    "print(scores[scores > 90])\n",
    "print(\"-\" * 30)\n",
    "\n",
    "# --- 3. 算术与统计 ---\n",
    "print(\"\\n--- 3. 算术与统计 ---\")\n",
    "print(\"分数的总和:\", scores.sum())\n",
    "print(\"分数的平均值:\", scores.mean())\n",
    "print(\"分数的最大值:\", scores.max())\n",
    "print(\"分数的最小值:\", scores.min())\n",
    "print(\"非空分数的数量:\", scores.count())\n",
    "print(\"分数的描述性统计:\")\n",
    "print(scores.describe())\n",
    "print(\"-\" * 30)\n",
    "\n",
    "# --- 4. 数据清洗与处理 ---\n",
    "print(\"\\n--- 4. 数据清洗与处理 ---\")\n",
    "print(\"检查哪些分数是缺失值:\")\n",
    "print(scores.isnull())\n",
    "print(\"删除缺失值后的 Series:\")\n",
    "scores_cleaned = scores.dropna()\n",
    "print(scores_cleaned)\n",
    "print(\"用 0 填充缺失值后的 Series:\")\n",
    "scores_filled = scores.fillna(0)\n",
    "print(scores_filled)\n",
    "\n",
    "# 替换某个值\n",
    "scores_replaced = scores.replace(92, 93) # 将所有92替换为93\n",
    "print(\"将 92 替换为 93 后的 Series:\")\n",
    "print(scores_replaced)\n",
    "print(\"-\" * 30)\n",
    "\n",
    "# --- 5. 排序与排名 ---\n",
    "print(\"\\n--- 5. 排序与排名 ---\")\n",
    "print(\"按分数降序排序:\")\n",
    "print(scores_cleaned.sort_values(ascending=False))\n",
    "print(\"按索引升序排序:\")\n",
    "print(scores.sort_index())\n",
    "print(\"计算分数的排名 (默认升序):\")\n",
    "print(scores.rank())\n",
    "print(\"-\" * 30)\n",
    "\n",
    "# --- 6. 唯一值与计数 ---\n",
    "print(\"\\n--- 6. 唯一值与计数 ---\")\n",
    "print(\"所有唯一分数:\", scores.unique())\n",
    "print(\"唯一分数的数量:\", scores.nunique())\n",
    "print(\"每个分数出现的次数:\")\n",
    "print(scores.value_counts())\n",
    "print(\"-\" * 30)\n",
    "\n",
    "# --- 7. 类型转换 (示例：转换为整数) ---\n",
    "print(\"\\n--- 7. 类型转换 ---\")\n",
    "# 注意：转换为整数时会因为NaN报错，需要先处理NaN\n",
    "scores_int = scores.fillna(0).astype(int)\n",
    "print(\"转换为整数类型后的Series (缺失值填充0):\")\n",
    "print(scores_int)\n",
    "print(\"-\" * 30)\n",
    "\n",
    "# --- 8. 应用函数 (apply) ---\n",
    "print(\"\\n--- 8. 应用函数 (apply) ---\")\n",
    "# 对每个分数进行加权（例如，加 5 分）\n",
    "scores_adjusted = scores.apply(lambda x: x + 5 if not pd.isna(x) else x)\n",
    "print(\"分数加 5 后的 Series:\")\n",
    "print(scores_adjusted)\n",
    "\n",
    "# 定义一个更复杂的函数\n",
    "def grade_level(score):\n",
    "    if pd.isna(score):\n",
    "        return '缺考'\n",
    "    elif score >= 90:\n",
    "        return '优秀'\n",
    "    elif score >= 80:\n",
    "        return '良好'\n",
    "    elif score >= 60:\n",
    "        return '及格'\n",
    "    else:\n",
    "        return '不及格'\n",
    "\n",
    "grades = scores.apply(grade_level)\n",
    "print(\"根据分数判断等级的 Series:\")\n",
    "print(grades)\n",
    "print(\"-\" * 30)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c67f0056-85f9-4d8f-9232-17d341b975fc",
   "metadata": {},
   "source": [
    "## DataFrame示例"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0fb1984c-2e8c-4f03-a67e-138b2e92ef5a",
   "metadata": {},
   "source": [
    "### 创建DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1265365a-baa7-45d8-9330-9e144285c881",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 从字典创建 DataFrame\n",
    "data = {\n",
    "    '姓名': ['张三', '李四', '王五', '赵六'],\n",
    "    '年龄': [25, 30, 22, 28],\n",
    "    '城市': ['北京', '上海', '广州', '深圳']\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "print(\"从字典创建的DataFrame:\")\n",
    "print(df)\n",
    "\n",
    "# 指定索引和列名创建 DataFrame\n",
    "dates = pd.date_range('20250701', periods=6)\n",
    "df2 = pd.DataFrame(np.random.randn(6, 4), index=dates, columns=list('ABCD'))\n",
    "print(\"\\n指定索引和列名创建的DataFrame:\")\n",
    "print(df2)\n",
    "\n",
    "# 从列表的列表创建 DataFrame\n",
    "data_list = [['张三', 25, '北京'], ['李四', 30, '上海']]\n",
    "df3 = pd.DataFrame(data_list, columns=['姓名', '年龄', '城市'])\n",
    "print(\"\\n从列表的列表创建的DataFrame:\")\n",
    "print(df3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df8352ea-9b5c-441a-a99d-2bc5801048d7",
   "metadata": {},
   "source": [
    "### DataFrame的基本操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95a8925a-542f-44d7-a62a-57b2a675150b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查看数据\n",
    "print(\"\\nDataFrame 头部数据:\")\n",
    "print(df.head(2)) # 查看前两行\n",
    "print(\"\\nDataFrame 尾部数据:\")\n",
    "print(df.tail(1)) # 查看后一行\n",
    "\n",
    "# 查看索引、列和值\n",
    "print(\"\\nDataFrame索引:\", df.index)\n",
    "print(\"DataFrame列名:\", df.columns)\n",
    "print(\"DataFrame值 (NumPy数组):\", df.values)\n",
    "\n",
    "# 查看数据类型\n",
    "print(\"\\nDataFrame数据类型:\")\n",
    "print(df.info())\n",
    "\n",
    "# 查看统计摘要\n",
    "print(\"\\nDataFrame统计摘要:\")\n",
    "print(df.describe())\n",
    "\n",
    "# 转置\n",
    "print(\"\\nDataFrame转置:\")\n",
    "print(df.T)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "272ecb5e-f295-4ea3-b0c9-2f97a482abf8",
   "metadata": {},
   "source": [
    "### DataFrame常用方法示例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c92ba2e3-6e04-4a40-af7f-28371fd49110",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 创建模拟数据\n",
    "data = {\n",
    "    '订单ID': [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010],\n",
    "    '商品': ['T恤', '牛仔裤', '鞋子', 'T恤', '帽子', '鞋子', '裙子', 'T恤', '牛仔裤', '鞋子'],\n",
    "    '地区': ['华东', '华北', '华南', '华东', '华北', '华东', '华南', '华中', '华北', '华东'],\n",
    "    '销量': [150, 120, 80, 200, 50, 90, 110, 180, 130, 70],\n",
    "    '单价': [50.0, 80.0, 120.0, 55.0, 30.0, 130.0, 90.0, 60.0, 85.0, 125.0],\n",
    "    '订单日期': pd.to_datetime(['2024-01-10', '2024-01-15', '2024-02-01', '2024-02-05',\n",
    "                                 '2024-02-10', '2024-03-01', '2024-03-05', '2024-03-10',\n",
    "                                 '2024-03-15', '2024-03-20']),\n",
    "    '备注': ['正常', np.nan, '促销', '正常', np.nan, '退货', '促销', '正常', '缺货', '正常']\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "# 设定 '订单ID' 为索引\n",
    "df = df.set_index('订单ID')\n",
    "\n",
    "# 1. DataFrame的创建与基本信息\n",
    "print(\"--- 1. DataFrame 的创建与基本信息 ---\")\n",
    "print(\"原始 DataFrame:\")\n",
    "print(df)\n",
    "print(\"\\nDataFrame 的形状 (行数, 列数):\", df.shape)\n",
    "print(\"DataFrame 的列名:\", df.columns.tolist())\n",
    "print(\"DataFrame 的索引:\", df.index.tolist())\n",
    "print(\"DataFrame 每列的数据类型:\")\n",
    "print(df.dtypes)\n",
    "print(\"\\nDataFrame 的信息摘要:\")\n",
    "df.info()\n",
    "print(\"\\nDataFrame 的头部 (前3行):\")\n",
    "print(df.head(3))\n",
    "print(\"\\nDataFrame 的尾部 (后2行):\")\n",
    "print(df.tail(2))\n",
    "print(\"\\nDataFrame 是否为空:\", df.empty)\n",
    "print(\"-\" * 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f44d409-9721-4ae1-a340-6a4a44c3e9b8",
   "metadata": {},
   "source": [
    "#### 数据选择与切片"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98bc21d0-b73e-4f81-9fc2-a72a96bf4916",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\\n--- 2. 数据选择与切片 ---\")\n",
    "# 选择单列\n",
    "print(\"选择 '商品' 列:\")\n",
    "print(df['商品'].head())\n",
    "\n",
    "# 选择多列\n",
    "print(\"\\n选择 '商品' 和 '销量' 列:\")\n",
    "print(df[['商品', '销量']].head())\n",
    "\n",
    "# 使用 .loc (基于标签)\n",
    "print(\"\\n使用 .loc 选择 '订单ID' 为 1003 的行:\")\n",
    "print(df.loc[1003])\n",
    "print(\"\\n使用 .loc 选择 '订单ID' 为 1001 到 1004 的 '商品' 和 '销量' 列:\")\n",
    "print(df.loc[1001:1004, ['商品', '销量']])\n",
    "\n",
    "# 使用 .iloc (基于位置)\n",
    "print(\"\\n使用 .iloc 选择第 1 行 (索引为 0) 的数据:\")\n",
    "print(df.iloc[0])\n",
    "print(\"\\n使用 .iloc 选择第 0 到 2 行，以及第 0 和 2 列:\")\n",
    "print(df.iloc[0:3, [0, 2]]) # 商品和销量列\n",
    "\n",
    "# 布尔索引\n",
    "print(\"\\n选择销量大于 100 的所有订单:\")\n",
    "print(df[df['销量'] > 100])\n",
    "print(\"\\n选择 '地区' 为 '华东' 且 '销量' 大于 100 的订单:\")\n",
    "print(df[(df['地区'] == '华东') & (df['销量'] > 100)])\n",
    "print(\"-\" * 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8a464ea-4c0e-4d99-825a-7cd78a403d5a",
   "metadata": {},
   "source": [
    "#### 数据清洗与处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71528c7c-4cd5-4c9c-b927-98543dfd1378",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\\n--- 3. 数据清洗与处理 ---\")\n",
    "# 检查缺失值\n",
    "print(\"检查 DataFrame 中的缺失值:\")\n",
    "print(df.isnull())\n",
    "print(\"\\n每列的缺失值数量:\")\n",
    "print(df.isnull().sum())\n",
    "\n",
    "# 填充缺失值\n",
    "df_filled = df.copy()\n",
    "df_filled['备注'] = df_filled['备注'].fillna('无备注')\n",
    "print(\"\\n填充 '备注' 列缺失值 (用 '无备注') 后的 DataFrame:\")\n",
    "print(df_filled)\n",
    "\n",
    "# 删除缺失值 (这里只演示，不修改原始df)\n",
    "df_dropna = df.dropna(subset=['备注']) # 只删除 '备注' 列有缺失值的行\n",
    "print(\"\\n删除 '备注' 列缺失值后的 DataFrame (只看前几行):\")\n",
    "print(df_dropna.head())\n",
    "\n",
    "# 添加重复行进行演示\n",
    "df_dup = df.copy()\n",
    "df_dup.loc[1011] = df.loc[1001] # 添加一行与 1001 相同的记录\n",
    "print(\"\\n带有重复行的 DataFrame (新添加 1011):\")\n",
    "print(df_dup)\n",
    "\n",
    "# 标记重复行\n",
    "print(\"\\n标记重复行 (保留第一个):\")\n",
    "print(df_dup.duplicated())\n",
    "print(\"\\n删除重复行后的 DataFrame:\")\n",
    "print(df_dup.drop_duplicates())\n",
    "\n",
    "# 替换值\n",
    "df_replaced = df.copy()\n",
    "df_replaced['地区'] = df_replaced['地区'].replace('华东', '华东区域')\n",
    "print(\"\\n将 '华东' 替换为 '华东区域' 后的 DataFrame:\")\n",
    "print(df_replaced['地区'])\n",
    "\n",
    "# 数据类型转换 (例如，将 '销量' 转换为浮点型，如果它不是的话)\n",
    "# df['销量'] = df['销量'].astype(float)\n",
    "# print(\"\\n'销量' 列转换为浮点型后的数据类型:\", df['销量'].dtype)\n",
    "print(\"-\" * 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebe8ed7e-1392-4385-ad45-693cad116c45",
   "metadata": {},
   "source": [
    "#### 算术与统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10ba239a-e0d1-4ab8-b630-011520b4ad74",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\\n--- 4. 算术与统计 ---\")\n",
    "# 新增一列：销售额\n",
    "df['销售额'] = df['销量'] * df['单价']\n",
    "print(\"新增 '销售额' 列后的 DataFrame:\")\n",
    "print(df.head())\n",
    "\n",
    "# 每列的平均值\n",
    "print(\"\\n每列的平均值:\")\n",
    "print(df[['销量', '单价', '销售额']].mean())\n",
    "\n",
    "# 每列的总和\n",
    "print(\"\\n每列的总和:\")\n",
    "print(df[['销量', '销售额']].sum())\n",
    "\n",
    "# 最大值和最小值\n",
    "print(\"\\n'销量' 的最大值:\", df['销量'].max())\n",
    "print(\"'单价' 的最小值:\", df['单价'].min())\n",
    "\n",
    "# 描述性统计\n",
    "print(\"\\n数值列的描述性统计:\")\n",
    "print(df.describe())\n",
    "\n",
    "# 计算列之间的相关性\n",
    "print(\"\\n'销量' 和 '单价' 之间的相关系数:\")\n",
    "print(df['销量'].corr(df['单价']))\n",
    "print(\"\\n所有数值列之间的相关矩阵:\")\n",
    "print(df.corr(numeric_only=True)) # numeric_only=True 用于只计算数值列\n",
    "print(\"-\" * 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44f705e7-73c6-47b4-8f32-ba85f0e201fc",
   "metadata": {},
   "source": [
    "#### 函数应用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b65f6cf6-0b2a-4d60-8015-dc0efe78bf98",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\\n--- 5. 函数应用 ---\")\n",
    "# 使用 apply 对列进行操作 (axis=0)\n",
    "# 根据销量判断商品受欢迎程度\n",
    "def popularity(sales):\n",
    "    if sales >= 150:\n",
    "        return '非常受欢迎'\n",
    "    elif sales >= 100:\n",
    "        return '受欢迎'\n",
    "    else:\n",
    "        return '一般'\n",
    "\n",
    "df['受欢迎程度'] = df['销量'].apply(popularity)\n",
    "print(\"新增 '受欢迎程度' 列后的 DataFrame:\")\n",
    "print(df[['销量', '受欢迎程度']])\n",
    "\n",
    "# 使用 apply 对行进行操作 (axis=1)\n",
    "# 计算每笔订单的毛利润 (假设毛利率为 30%)\n",
    "df['毛利润'] = df.apply(lambda row: row['销售额'] * 0.30, axis=1)\n",
    "print(\"\\n新增 '毛利润' 列后的 DataFrame:\")\n",
    "print(df[['销售额', '毛利润']].head())\n",
    "\n",
    "# 使用 applymap 对每个单元格操作 (只适用于元素级别的操作)\n",
    "# 将所有数值都加 100 (不常用，但用于演示)\n",
    "# numeric_df = df.select_dtypes(include=np.number)\n",
    "# df_plus_100 = numeric_df.applymap(lambda x: x + 100 if not pd.isna(x) else x)\n",
    "# print(\"\\n所有数值加 100 后的 DataFrame (部分列):\")\n",
    "# print(df_plus_100.head())\n",
    "print(\"-\" * 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a16d6989-2de2-4ede-98e7-ac376e81a8df",
   "metadata": {},
   "source": [
    "#### 排序与排名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "807ae805-54c3-46bd-80a0-9f664dd1b011",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\\n--- 6. 排序与排名 ---\")\n",
    "# 按单列排序\n",
    "print(\"按 '销量' 降序排序:\")\n",
    "print(df.sort_values(by='销量', ascending=False).head())\n",
    "\n",
    "# 按多列排序\n",
    "print(\"\\n按 '地区' 升序，然后按 '销售额' 降序排序:\")\n",
    "print(df.sort_values(by=['地区', '销售额'], ascending=[True, False]).head())\n",
    "\n",
    "# 按索引排序\n",
    "print(\"\\n按索引降序排序:\")\n",
    "print(df.sort_index(ascending=False).head())\n",
    "\n",
    "# 排名\n",
    "df['销量排名'] = df['销量'].rank(ascending=False, method='min') # 销量高的排名靠前，并使用'min'方法处理并列\n",
    "print(\"\\n新增 '销量排名' 列:\")\n",
    "print(df[['销量', '销量排名']].sort_values(by='销量', ascending=False))\n",
    "print(\"-\" * 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5924ea0a-38f3-4fb9-b174-d993b149d2c2",
   "metadata": {},
   "source": [
    "#### 数据合并与连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ea88e8c-c582-4573-ae1c-501dfe7df6a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\\n--- 7. 数据合并与连接 ---\")\n",
    "# 示例数据\n",
    "df_jan = df[df['订单日期'].dt.month == 1].copy()\n",
    "df_feb = df[df['订单日期'].dt.month == 2].copy()\n",
    "\n",
    "# concat (行连接)\n",
    "print(\"按行连接一月和二月的数据 (concat):\")\n",
    "df_jan_feb = pd.concat([df_jan, df_feb])\n",
    "print(df_jan_feb)\n",
    "\n",
    "# concat (列连接 - 需要对齐索引或使用 ignore_index=True)\n",
    "# 这里为了演示，我们创建一个有相同索引的辅助DataFrame\n",
    "additional_info = pd.DataFrame({\n",
    "    '客户满意度': ['高', '中', '高', '高', '低', '中', '高', '中', '高', '中']\n",
    "}, index=df.index)\n",
    "df_combined_cols = pd.concat([df, additional_info], axis=1)\n",
    "print(\"\\n按列连接 DataFrame (concat):\")\n",
    "print(df_combined_cols.head())\n",
    "\n",
    "# merge (类似于 SQL JOIN)\n",
    "# 创建一个商品类别信息 DataFrame\n",
    "product_categories = pd.DataFrame({\n",
    "    '商品': ['T恤', '牛仔裤', '鞋子', '帽子', '裙子'],\n",
    "    '类别': ['服装', '服装', '鞋类', '配饰', '服装']\n",
    "})\n",
    "\n",
    "df_merged = pd.merge(df, product_categories, on='商品', how='left')\n",
    "print(\"\\n通过 '商品' 列进行左连接 (merge):\")\n",
    "print(df_merged.head())\n",
    "\n",
    "# 示例：内连接（只保留共同的商品）\n",
    "product_price_update = pd.DataFrame({\n",
    "    '商品': ['T恤', '牛仔裤', '手机'], # 注意这里有一个'手机'\n",
    "    '新单价': [52.0, 82.0, 1000.0]\n",
    "})\n",
    "df_inner_merged = pd.merge(df, product_price_update, on='商品', how='inner')\n",
    "print(\"\\n通过 '商品' 列进行内连接 (merge - 只保留共同商品):\")\n",
    "print(df_inner_merged)\n",
    "print(\"-\" * 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "edb4e2e9-8772-49bb-beb2-029cac267a37",
   "metadata": {},
   "source": [
    "#### 分组与聚合 (GroupBy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "426a8030-c429-433d-8735-a142d467a975",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\\n--- 8. 分组与聚合 (GroupBy) ---\")\n",
    "# 按 '地区' 分组，计算每个地区的总销量\n",
    "print(\"按 '地区' 分组，计算总销量:\")\n",
    "print(df.groupby('地区')['销量'].sum())\n",
    "\n",
    "# 按 '商品' 分组，计算每个商品的平均单价和总销售额\n",
    "print(\"\\n按 '商品' 分组，计算平均单价和总销售额:\")\n",
    "print(df.groupby('商品').agg(\n",
    "    平均单价=('单价', 'mean'),\n",
    "    总销售额=('销售额', 'sum'),\n",
    "    订单数量=('订单ID', 'count')\n",
    "))\n",
    "\n",
    "# 多重分组\n",
    "print(\"\\n按 '地区' 和 '商品' 分组，计算总销量:\")\n",
    "print(df.groupby(['地区', '商品'])['销量'].sum())\n",
    "print(\"-\" * 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e54e5ffd-cace-4c28-bcf1-a9bd348d41f5",
   "metadata": {},
   "source": [
    "#### 数据重塑与透视 (Pivot, Pivot Table, Stack/Unstack)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b509f361-2a9a-4e0a-932c-07d7f3ea1396",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\\n--- 9. 数据重塑与透视 ---\")\n",
    "# 创建一个简化的DataFrame用于透视表演示\n",
    "df_pivot_data = df[['订单日期', '地区', '销量']].copy()\n",
    "df_pivot_data['订单月份'] = df_pivot_data['订单日期'].dt.month\n",
    "\n",
    "# 数据透视表 (pivot_table)\n",
    "# 按月份和地区汇总销量\n",
    "pivot_table = pd.pivot_table(df_pivot_data,\n",
    "                             values='销量',\n",
    "                             index='订单月份',\n",
    "                             columns='地区',\n",
    "                             aggfunc='sum',\n",
    "                             fill_value=0)\n",
    "print(\"按月份和地区汇总销量的透视表:\")\n",
    "print(pivot_table)\n",
    "\n",
    "# pivot (更简单的重塑，需要唯一索引)\n",
    "# 例如，想看每个商品的各地区销量，但要求地区和商品组合唯一\n",
    "# df_pivot_simple = df.pivot(index='商品', columns='地区', values='销量')\n",
    "# print(\"\\n简单的 pivot 示例:\")\n",
    "# print(df_pivot_simple)\n",
    "\n",
    "\n",
    "# Stack/Unstack (从“宽”到“长”和从“长”到“宽”的转换)\n",
    "# 先将透视表堆叠 (stack)\n",
    "stacked_pivot = pivot_table.stack()\n",
    "print(\"\\n堆叠 (Stack) 后的数据:\")\n",
    "print(stacked_pivot)\n",
    "\n",
    "# 再将堆叠后的数据展开 (unstack)\n",
    "unstacked_pivot = stacked_pivot.unstack()\n",
    "print(\"\\n展开 (Unstack) 后的数据 (恢复原样):\")\n",
    "print(unstacked_pivot)\n",
    "print(\"-\" * 50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1cb28b5-d1a3-424c-9590-162ff86029db",
   "metadata": {},
   "source": [
    "## 案例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c23bf19-4e5e-46d3-92c3-16d8d0d20b74",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import time, gc, random, datetime\n",
    "import os, random, re, time, sys\n",
    "\n",
    "pd.set_option('display.max_columns', 200)\n",
    "pd.set_option('display.max_rows', 200)\n",
    "\n",
    "df_trade = pd.read_csv('./(sample)sam_tianchi_mum_baby_trade_history.csv')\n",
    "df_baby = pd.read_csv('./(sample)sam_tianchi_mum_baby.csv')\n",
    "\n",
    "display(df_trade.head(5))  # 显示trade_history表的前5行\n",
    "display(df_baby.head(5))  # 显示baby表的前5行\n",
    "\n",
    "# 显示两个表的形状\n",
    "print(f\"trade_history表形状：{df_trade.shape}\")\n",
    "print(f\"baby表形状：{df_baby.shape}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efbcec8e-03a7-4578-8a3d-c2a1befd1541",
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_date(df, column):\n",
    "    df[column + '_date'] = df[column].apply(lambda x: int(str(x).replace('-','')[:8]))\n",
    "    df[column] = pd.to_datetime(df[column].astype('str'))\n",
    "    df[column + '_y'] = df[column].dt.year #年\n",
    "    df[column + '_q'] = df[column].dt.quarter #季\n",
    "    df[column + '_m'] = df[column].dt.month #月\n",
    "    df[column + '_d'] = df[column].dt.day #月\n",
    "    df[column + '_w'] = df[column].dt.weekday #星期\n",
    "    return df\n",
    "\n",
    "df_trade = convert_date(df_trade, 'day')\n",
    "df_baby = convert_date(df_baby, 'birthday')\n",
    "\n",
    "display(df_trade.head(5))  # 显示转换日期后trade_history表的前5行\n",
    "display(df_baby.head(5))  # 显示转换日期后baby表的前5行\n",
    "\n",
    "# 显示转换日期后两个表的形状\n",
    "print(f\"trade_history表转换格式后的形状：{df_trade.shape}\")\n",
    "print(f\"baby表转换格式后形状：{df_baby.shape}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "277bf35e-83e5-4def-8d05-02e6fafbbb4d",
   "metadata": {},
   "source": [
    "### 过滤数据\n",
    "- 以18岁为成年算起，2007年之前出生的宝宝已然成年；\n",
    "- 性别无法确定的数据行，予以删除\n",
    "- 购买数量超过5倍标准差的，对数据影响比较大，予以排除"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2366b494-17c0-41fe-bb1a-e124861ce56e",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"原始trade_history表的大小：{df_trade.shape}，原始baby表的大小：{df_baby.shape}\")\n",
    "\n",
    "# \n",
    "df_baby = df_baby.query(\"birthday_y>=2007 and gender <=1\")\n",
    "df_trade = df_trade.query(f\"buy_mount < {df_trade.buy_mount.std()*5}\")\n",
    "\n",
    "print(f\"过滤后trade_history表的大小：{df_trade.shape}，过滤后baby表的大小：{df_baby.shape}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89456796-6976-478e-8248-a0954850abb7",
   "metadata": {},
   "source": [
    "### 销量分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f705023a-5cb4-4041-9bc5-18f8ba01a8fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用 plt.subplots() 创建 Figure 和 Axes 对象\n",
    "fig, axes = plt.subplots(nrows=1, ncols=2, figsize=(20, 8))\n",
    "\n",
    "# --- 第一个子图 (axes[0]) ---\n",
    "y_sales = df_trade.groupby('day_y')['buy_mount'].sum().iloc[1:-1]\n",
    "x_list = y_sales.index.tolist()\n",
    "\n",
    "# 为每个年度的销售额生成不同的颜色\n",
    "# 可以使用 Matplotlib 内置的颜色循环，或自定义颜色列表\n",
    "colors1 = plt.cm.viridis(np.linspace(0, 1, len(x_list))) # 使用viridis颜色映射生成颜色\n",
    "axes[0].bar(x_list, y_sales.values, color=colors1) # 将颜色列表传递给 color 参数\n",
    "axes[0].set_xlabel('Year')\n",
    "axes[0].set_ylabel('Sales Volume')\n",
    "axes[0].set_title('Sales Volume By Year')\n",
    "axes[0].set_xticks(x_list)\n",
    "\n",
    "# 环比增长 (在 axes[0] 的第二个 Y 轴上绘制)\n",
    "ax1 = axes[0].twinx()\n",
    "y_sales_pct = y_sales.pct_change().fillna(0) * 100\n",
    "ax1.plot(x_list, y_sales_pct.values, color='red', marker='o', linestyle='--') # 为折线图添加标记和样式\n",
    "\n",
    "# --- 第二个子图 (axes[1]) ---\n",
    "y_sales = df_trade.groupby(['day_y', 'day_q'])['buy_mount'].sum().iloc[1:-1]\n",
    "x_list = [f'{str(x[0])[-2:]}/Q{x[1]}' for x in y_sales.index]\n",
    "\n",
    "# 为每个季度销售额生成不同的颜色\n",
    "colors2 = plt.cm.plasma(np.linspace(0, 1, len(x_list))) # 使用plasma颜色映射生成颜色\n",
    "axes[1].bar(x_list, y_sales.values, color=colors2) # 将颜色列表传递给 color 参数\n",
    "axes[1].set_xlabel('Season')\n",
    "axes[1].set_ylabel('Sales Volume')\n",
    "axes[1].set_title('Sales Volume Year-Season')\n",
    "axes[1].set_xticks(x_list)\n",
    "axes[1].tick_params(axis='x', rotation=45) # 旋转 x 轴标签，防止重叠\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a110c98d-afae-4c52-adc5-fc6996e8dc45",
   "metadata": {},
   "source": [
    "### 各月的销量可按如下方式进行展示"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1cd036e-2723-4029-ad33-d6f83f40e364",
   "metadata": {},
   "source": [
    "fig = plt.figure(figsize=(20,5) )\n",
    "ax = fig.add_subplot()\n",
    "y_sales = df_trade.groupby(['day_y', 'day_m'])['buy_mount'].sum().iloc[1:-1]\n",
    "x_list = [f'{str(x[0])[-2:]}/{x[1]}' for x in y_sales.index]\n",
    "for x,y in zip(x_list, y_sales.values):\n",
    "    plt.bar(x, y)\n",
    "\n",
    "#6个月的移动平均线\n",
    "sales_ma6 = y_sales.rolling(6, 1).mean()\n",
    "plt.plot(x_list, sales_ma6.values)  \n",
    "\n",
    "ax.set_xlabel('Month')\n",
    "ax.set_ylabel('Seles Volume')\n",
    "ax.set_title('Sales Volume Year-Month')\n",
    "ax.set_xticks(x_list)\n",
    "plt.plot()\n",
    "\n",
    "sales_temp = pd.concat((y_sales, sales_ma6), axis=1)\n",
    "sales_temp.columns = ['buy_mount', 'buy_mount_ma6']\n",
    "sales_temp.query(\"buy_mount / buy_mount_ma6>1.3\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2586ff04-46f3-4713-9c89-0570218f50af",
   "metadata": {},
   "source": [
    "## 2014年11月份每天的销量情况"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2051b0f-a42d-43ca-bfa3-1f56824df8ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure(figsize=(20,5) )\n",
    "ax = fig.add_subplot()\n",
    "y_sales = df_trade.query('day_y==2014 and day_m == 11').groupby('day_d')['buy_mount'].sum()\n",
    "x_list = y_sales.index.tolist()\n",
    "for x,y in zip(x_list, y_sales.values):\n",
    "    plt.bar(x, y)\n",
    "\n",
    "ax.set_xlabel('Day')\n",
    "ax.set_ylabel('Seles Volume')\n",
    "ax.set_title('2014-11 Sales Volume')\n",
    "ax.set_xticks(x_list)\n",
    "plt.plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48105920-733a-4a17-9b81-77a125c1dfa0",
   "metadata": {},
   "source": [
    "### 类目分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0283f8af-48c5-492c-a024-89a7a4798af7",
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_num = df_trade.groupby('cat1')['cat_id'].nunique().to_frame()\n",
    "cat_num.columns = ['分类数量']\n",
    "cat_num.index.name = '大类名称'\n",
    "print('分类详情')\n",
    "display(cat_num)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19a75777-78e2-4a30-8545-a1b3ac7f688e",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
